﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OleDb;
using System.Data;
using System.Windows.Forms;

namespace Imenik_Projektni_2011
{
     static class Funkcije
    {
        
         /// <summary>
         /// Ova metoda popunjava dataset sa dostavljenom komandom
         /// </summary>
         /// <param name="conn">Konekcija do baze</param>
         /// <param name="command">Komanda</param>
         /// <returns>Dataset</returns>
         public static  DataSet fillDataset (OleDbConnection conn, string command)
         {
             DataSet ds = null;
             OleDbCommand cmd = new OleDbCommand();
             cmd.Connection = conn;
             cmd.CommandText = command;
             OleDbDataAdapter da;
             da = new OleDbDataAdapter(cmd);
             ds = new DataSet();
             da.Fill(ds);
             return ds;
         }

         /// <summary>
         /// Vraca true ako je podatak nadjen
         /// </summary>
         /// <param name="ds">Dataset</param>
         /// <param name="collumnName">Kolona u kojoj se trazi</param>
         /// <param name="data">Podatak koji se trazi</param>
         /// <returns>bool</returns>
         public static bool checkForData(DataSet ds,string collumnName, string data)
         {
             DataTable tbl = ds.Tables[0];
             foreach (DataRow slog in tbl.Rows)
             {
                 if (data == slog[collumnName].ToString())
                 {
                     
                     return true;
                 }
             }
             return false;
         }

         /// <summary>
         /// Vraća integer vrednost na osnovu dostavljene komande koja mora biti tipa count, avg, sum. . .
         /// </summary>
         /// <param name="ds">Dataset</param>
         /// <param name="conn">Konekcija</param>
         /// <param name="command">Komanda koja vraca 1 podatak</param>
         /// <returns>integer</returns>
         public static int returnSingleInt(DataSet ds, OleDbConnection conn, string command)
         {
             OleDbCommand cmd = new OleDbCommand();
             cmd.Connection = conn;
             cmd.CommandText = command;

             OleDbDataAdapter da;
             da = new OleDbDataAdapter(cmd);
             ds = new DataSet();
             da.Fill(ds);
             int result = 0;
             result = int.Parse(ds.Tables[0].Rows[0][0].ToString());

             return result;
         }


         /// <summary>
         /// Vraca tekstualni podatak
         /// </summary>
         /// <param name="ds">Dataset</param>
         /// <param name="conn">Konekcija</param>
         /// <param name="command">Komanda koja vraca jednu vrednost</param>
         /// <returns>string</returns>
         public static string returnSingleString(DataSet ds, OleDbConnection conn, string command)
         {
             OleDbCommand cmd = new OleDbCommand();
             cmd.Connection = conn;
             cmd.CommandText = command;

             OleDbDataAdapter da;
             da = new OleDbDataAdapter(cmd);
             ds = new DataSet();
             da.Fill(ds);
             string result = "";
             result = ds.Tables[0].Rows[0][0].ToString();

             return result;
         }

         /// <summary>
         /// Vraca textbox promenljivu sa bindovanim podatkom
         /// </summary>
         /// <param name="ds">Dataset</param>
         /// <param name="conn">Konekcija</param>
         /// <param name="temp">Textbox koji se povezuje</param>
         /// <param name="command">Komanda koja vraca jedan red</param>
         /// <param name="column">Kolona koja se binduje</param>
         /// <returns>Textbox</returns>
         public static TextBox fillTextBox(DataSet ds, OleDbConnection conn, string command ,TextBox temp  , string column)
         {
             TextBox temp1 = temp;
             OleDbCommand cmd = new OleDbCommand();
             cmd.Connection = conn;
             cmd.CommandText = command;

             OleDbDataAdapter da;
             da = new OleDbDataAdapter(cmd);
             ds = new DataSet();
             da.Fill(ds);
             temp.DataBindings.Add("Text", ds.Tables[0], column);
             

             return temp1;
         }


         /// <summary>
         /// Brise red iz tabele
         /// </summary>
         /// <param name="ds">Dataset</param>
         /// <param name="conn">Konekcija</param>
         /// <param name="command">Komanda za brisanje</param>
         /// <param name="data">Proverava da li se ovaj podatak nalazi u tabeli</param>
         /// <param name="column">Koja se kolona proverava</param>
         /// <returns>Dataset</returns>
         public static DataSet delete(DataSet ds, OleDbConnection conn, string command, string data, string column)
         {

             OleDbCommand cmd = new OleDbCommand("select * from contacts", conn);
            OleDbDataAdapter da;
             da = new OleDbDataAdapter(cmd);
             ds = new DataSet();
             da.Fill(ds);


             DataTable tbl = ds.Tables[0];
             bool nalaz = false;
             foreach (DataRow slog in tbl.Rows)
             {
                 if (data == slog[column].ToString())
                 {
                     nalaz = true;
                 }
             }
             if (!nalaz)
             {
                 MessageBox.Show("No data found");
                 return ds;
             }
             
             cmd = new OleDbCommand();
             cmd.Connection = conn;
             cmd.CommandText = command;
             
             //cmd.CommandText = String.Format("DELETE FROM pasosi WHERE brojpasosa = ({0})", txtIBrojPasosa.Text); // ('{0}') ne ovako jer je id = integer
             conn.Open();
             cmd.ExecuteNonQuery();
             
             da = new OleDbDataAdapter(cmd);
             da.Fill(ds);
             conn.Close();
             return ds;
             
         }

         /// <summary>
         /// Izmenjuje tabelu
         /// </summary>
         /// <param name="ds">Dataset</param>
         /// <param name="conn">Konekcija</param>
         /// <param name="command">Komanda</param>
         /// <returns>Dataset</returns>
         public static DataSet update(DataSet ds, OleDbConnection conn, string command)
         {
             //cmd.CommandText = String.Format
             //   ("update pasosi set ime = '{0}', prezime = '{1}', datumrodjenja = '{2}', mestorodjenja = '{3}', jmbg = '{4}' where brojpasosa = {5}",
             //    txtIIme.Text, txtIPrezime.Text, txtIdatumRodjenja.Text, txtIMestoRodjenja.Text, txtIJmbg.Text, txtIBrojPasosa.Text); 

             OleDbCommand cmd = new OleDbCommand();
             cmd.Connection = conn;
             cmd.CommandText = command;
             conn.Open();
             cmd.ExecuteNonQuery();
             OleDbDataAdapter da;
             da = new OleDbDataAdapter(cmd);
             da.Fill(ds);
             conn.Close();
             return ds;

         }

         /// <summary>
         /// Ubacuje podatke u tabelu users
         /// </summary>
         /// <param name="ds">Dataset</param>
         /// <param name="conn">Connection</param>
         /// <param name="row">Kolekcija stringova</param>
         /// <param name="data">Proverava da li se podatak nalazi u bazi</param>
         /// <param name="column">Kolona koju proverava</param>
         /// <returns>Dataset</returns>
         public static void add(ref DataSet ds, OleDbConnection conn, string[] row, string data, string column)
         {
             DataTable tbl = ds.Tables[0];
             bool nalaz = false;
             foreach (DataRow slog in tbl.Rows)
             {
                 if (data == slog[column].ToString())
                 {
                     nalaz = true;
                 }
             }
             if (nalaz)
             {
                 MessageBox.Show("There is row in database with that criterium");
                 
             }

             OleDbCommand cmd = new OleDbCommand();
             cmd.Connection = conn;
             cmd.CommandText = string.Format
             (@"insert into users (id, [username], [password], ime, prezime, slika) values ('{0}', '{1}', '{2}', '{3}', '{4}', '{5}')", row[0], row[1], row[2], row[3], row[4], row[5]);

            //("Insert into pasosi (brojpasosa, ime, Prezime, datumrodjenja, mestorodjenja, jmbg) values ('{0}', '{1}', '{2}', '{3}', '{4}', '{5}')",
            //    txtBrojPasosa.Text, txtIme.Text, txtPrezime.Text, txtDatumRodjenja.Text, txtMestoRodjenja.Text, txtJmbg.Text);
           
             conn.Open();
            
             OleDbDataAdapter da;
             da = new OleDbDataAdapter(cmd);
             ds.Clear();
             da.Fill(ds);
             conn.Close();
             

         }

         /// <summary>
         /// Ubacuje podatke u tabelu contacts
         /// </summary>
         /// <param name="ds">Dataset</param>
         /// <param name="conn">Connection</param>
         /// <param name="row">Kolekcija stringova</param>
         /// <param name="data">Proverava da li se podatak nalazi u bazi</param>
         /// <param name="column">Kolona koju proverava</param>
         /// <returns>Dataset</returns>
         public static void addContact(ref DataSet ds, OleDbConnection conn, string[] row, string data, string column)
         {
             DataTable tbl = ds.Tables[0];
             bool nalaz = false;
             foreach (DataRow slog in tbl.Rows)
             {
                 if (data == slog[column].ToString())
                 {
                     nalaz = true;
                 }
             }
             if (nalaz)
             {
                 MessageBox.Show("There is row in database with that criterium");
                 return;
             }

             OleDbCommand cmd = new OleDbCommand();
             cmd.Connection = conn;
             cmd.CommandText = string.Format
             (@"insert into contacts (id, [idusers], fullname, phone, city, facebook, youtube, picture, email, notes) 
              values ('{0}', '{1}', '{2}', '{3}', '{4}', '{5}','{6}','{7}','{8}','{9}')", 
                row[0], row[1], row[2], row[3], row[4], row[5], row[6], row[7], row[8], row[9]);

             //("Insert into pasosi (brojpasosa, ime, Prezime, datumrodjenja, mestorodjenja, jmbg) values ('{0}', '{1}', '{2}', '{3}', '{4}', '{5}')",
             //    txtBrojPasosa.Text, txtIme.Text, txtPrezime.Text, txtDatumRodjenja.Text, txtMestoRodjenja.Text, txtJmbg.Text);

             conn.Open();

             OleDbDataAdapter da;
             da = new OleDbDataAdapter(cmd);
             ds.Clear();
             da.Fill(ds);
             conn.Close();
             
         }

         /// <summary>
         /// Vraca dostupan ID contakta
         /// </summary>
         /// <param name="ds">Dataset</param>
         /// <param name="conn">Konekcija</param>
         /// <returns>Integer</returns>
         public static int getAvailableID(DataSet ds, OleDbConnection conn)
         {
             OleDbCommand cmd = new OleDbCommand("select * from contacts", conn);
             OleDbDataAdapter da;
             da = new OleDbDataAdapter(cmd);
             ds = new DataSet();
             da.Fill(ds);

             DataTable tbl = ds.Tables[0];
             int max = 0;
                 foreach (DataRow slog in tbl.Rows)
                 {
                     if (max <= Convert.ToInt32(slog[0]))
                     {
                         max = Convert.ToInt32(slog[0]);
                     }
                 }            
             return max+1;
         }

         /// <summary>
         /// Vraca dostupan ID usersa
         /// </summary>
         /// <param name="ds">Dataset</param>
         /// <param name="conn">Konekcija</param>
         /// <returns>Integer</returns>
         public static int getAvailableIDUsers(DataSet ds, OleDbConnection conn)
         {
             OleDbCommand cmd = new OleDbCommand("select * from users", conn);
             OleDbDataAdapter da;
             da = new OleDbDataAdapter(cmd);
             ds = new DataSet();
             da.Fill(ds);

             DataTable tbl = ds.Tables[0];
             int max = 0;
             foreach (DataRow slog in tbl.Rows)
             {
                 if (max <= Convert.ToInt32(slog[0]))
                 {
                     max = Convert.ToInt32(slog[0]);
                 }
             }
             return max + 1;
         }


         /// <summary>
         /// Pupunjava kolekciju kontakta sa podacima iz baze
         /// </summary>
         /// <param name="rKontakti">promenljiva tipa datareader</param>
         /// <returns>Listu kontakta</returns>
         public static List<Kontakt> getContacts (OleDbDataReader rKontakti)
     {
         List<Kontakt> listaKontakt = new List<Kontakt>();

         while (rKontakti.Read())
         {
             Kontakt kontakt = new Kontakt();
             kontakt.ID = rKontakti.GetInt32(rKontakti.GetOrdinal("ID"));
             kontakt.IDUsers = rKontakti.GetInt32(rKontakti.GetOrdinal("IDUsers"));
             kontakt.FullName = rKontakti.GetString(rKontakti.GetOrdinal("FullName"));
             kontakt.Phone = rKontakti.GetString(rKontakti.GetOrdinal("Phone"));
             kontakt.City = rKontakti.GetString(rKontakti.GetOrdinal("City"));
             kontakt.Facebook = rKontakti.GetString(rKontakti.GetOrdinal("Facebook"));
             kontakt.Youtube = rKontakti.GetString(rKontakti.GetOrdinal("Youtube"));
             kontakt.Picture = rKontakti.GetString(rKontakti.GetOrdinal("Picture"));
             kontakt.Email = rKontakti.GetString(rKontakti.GetOrdinal("Email"));
             kontakt.Notes = rKontakti.GetString(rKontakti.GetOrdinal("Notes"));


             listaKontakt.Add(kontakt);
         }
         return listaKontakt;
     }
    }
}
